package demo.btgal;

import cn.lily.com.db.container.reader.DataTable;
import cn.lily.com.db.sql.manager.DBOfflineManager;
import cn.lily.com.enviroment.Enviroment;
import cn.lily.com.file.excel.css.ExcelWriterStyle;
import cn.lily.com.file.excel.io.ExcelWriter;
import cn.lily.com.file.excel.spe.IExcelBorder;
import cn.lily.com.file.excel.spe.IExcelColor;
import cn.lily.com.lang.Basic;

/**
 * 磁链分析
 * 18禁ゲーム
 * @author JiaJ
 * @date 2018年6月27日
 *
 */
public class BtGalAnalyse {
	
	
	
	/**
	 * 信息分析
	 */
	public void analyse(){
		

		
		ExcelWriter ew = new ExcelWriter("D:/bt_gal.xlsx");
		ew.inst(100);
		
		ExcelWriterStyle titleSt = new ExcelWriterStyle(ew);
		titleSt.getCell().setBorder("top", IExcelBorder.BORDER_DOUBLE)
		                 .setBorder("bottom", IExcelBorder.BORDER_DOUBLE)
		                 .setBorder("left", IExcelBorder.BORDER_DOUBLE)
		                 .setBorder("right", IExcelBorder.BORDER_DOUBLE)
		                 .setBackGroundColor(IExcelColor.GREY_50_PERCENT);
		
		ExcelWriterStyle successSt = new ExcelWriterStyle(ew);
		successSt.getCell().setBorder("top", IExcelBorder.BORDER_DOUBLE)
		                 .setBorder("bottom", IExcelBorder.BORDER_DOUBLE)
		                 .setBorder("left", IExcelBorder.BORDER_DOUBLE)
		                 .setBorder("right", IExcelBorder.BORDER_DOUBLE)
		                 .setBackGroundColor(IExcelColor.LIGHT_GREEN);
		
		ExcelWriterStyle warningSt = new ExcelWriterStyle(ew);
		warningSt.getCell().setBorder("top", IExcelBorder.BORDER_DOUBLE)
		                 .setBorder("bottom", IExcelBorder.BORDER_DOUBLE)
		                 .setBorder("left", IExcelBorder.BORDER_DOUBLE)
		                 .setBorder("right", IExcelBorder.BORDER_DOUBLE)
		                 .setBackGroundColor(IExcelColor.LIGHT_ORANGE);
		
		ExcelWriterStyle dangerSt = new ExcelWriterStyle(ew);
		dangerSt.getCell().setBorder("top", IExcelBorder.BORDER_DOUBLE)
		                 .setBorder("bottom", IExcelBorder.BORDER_DOUBLE)
		                 .setBorder("left", IExcelBorder.BORDER_DOUBLE)
		                 .setBorder("right", IExcelBorder.BORDER_DOUBLE)
		                 .setBackGroundColor(IExcelColor.LIGHT_CORNFLOWER_BLUE);
		
		int sheetNo = 0;
		ew.locateSheet(0);
		ew.renameSheet(sheetNo+"");
		
		int row = 0;
		DataTable hcnTable = DBOfflineManager.getDBManager().executeQueryTable(" select GAME_NAME,to_char(GAME_DATE,'yyyy-mm-dd') GAME_DATE "
				                                                             + " from ${schema}.LEO_HGAMECN_INFO order by GAME_DATE desc");
		while(hcnTable.next()){
			
			ew.locateRow(row);
			
			if(row == 0){
				
				ew.setCellValue(0,"游戏名");
				ew.setCellValue(1,"发售日期");
				ew.setCellValue(2,"存在匹配");
				ew.setCellValue(3,"喵窝匹配");
				ew.setStyle(0, titleSt);
				ew.setStyle(1, titleSt);
				ew.setStyle(2, titleSt);
				ew.setStyle(3, titleSt);
				
				ew.setColWith(0, 500);
				ew.setColWith(1, 100);
				ew.setColWith(2, 400);
				ew.setColWith(3, 500);
				
				ew.freezeFirstRow();
				row++;
				ew.locateRow(row);
			}
			
			ew.setCellValue(0,hcnTable.getString("GAME_NAME"));
			ew.setCellValue(1,hcnTable.getString("GAME_DATE"));
			
			String name = hcnTable.getString("GAME_NAME");
			
			System.out.println(name+"开始处理.........");
			
			String existName = "";
			String existSQL = "select * from ${schema}.LEO_GALGAME_INFO_JPS where (upper(GAME_NAME) like '" + Basic.toWhereLike(name.trim().toUpperCase()) + "' or upper(GAME_NAME_PLUS) like '" + Basic.toWhereLike(name.trim().toUpperCase()) + "')";
			DataTable newTable = DBOfflineManager.getDBManager().executeQueryTable(existSQL);
			while(newTable.next()){
				existName += newTable.getString("GAME_NAME")+",";
				existName += newTable.getString("GAME_NAME_PLUS")+",";
			}
			
			String yuinyaName = "";
			String yuinyaSQL = "select * from ${schema}.LEO_YUINYA_GAL_INFO where upper(GAME_NAME) like '" + Basic.toWhereLike(name.trim().toUpperCase()) + "'";
			DataTable yuinyaTable = DBOfflineManager.getDBManager().executeQueryTable(yuinyaSQL);
			while(yuinyaTable.next()){
				yuinyaName += yuinyaTable.getString("GAME_NAME")+",";
			}
			
			ew.setCellValue(2,existName);
			ew.setCellValue(3,yuinyaName);
			
			if(existName != null && !"".equals(existName)){
				ew.setStyle(0, successSt);
				ew.setStyle(1, successSt);
				ew.setStyle(2, successSt);
				ew.setStyle(3, successSt);
			}
			
			else if(yuinyaName != null && !"".equals(yuinyaName)){
				ew.setStyle(0, warningSt);
				ew.setStyle(1, warningSt);
				ew.setStyle(2, warningSt);
				ew.setStyle(3, warningSt);
			}
			
			else{
				ew.setStyle(0, dangerSt);
				ew.setStyle(1, dangerSt);
				ew.setStyle(2, dangerSt);
				ew.setStyle(3, dangerSt);
			}
			
			row++;
			if(row % 300 == 0){				
				sheetNo ++;
				ew.locateSheet(sheetNo);
				ew.renameSheet(sheetNo+"");
				row = 0;			
			}
			
		}
		
		ew.write();		
		ew.free();
		
		
	}
	
	public static void main(String[] args) {
		Enviroment.createEnv();
		Enviroment.getEnv().getLogBean().beginLog();
		
		BtGalAnalyse lyse = new BtGalAnalyse();
		lyse.analyse();
	}
}
